大数据导入之MySql设计之空间换时间的设计变更

最近几天一直在纠结于一个大数据批量导入的问题，经过几天思考，发现基于小数据情况，原本的数据结构设计是没有问题的，但是在大量数据导入，问题就很大了。我之前一直在强调“程序=数据结构+算法”，但在这此却钻了牛角尖，最后去仔细看了之前别人设计的数据表才突然灵光一现，发现了mysql层面要以空间换时间的具体设计思路。

基于这种情况，先说一下经过这个过程后的感悟。

感悟

1. 后续的程序逻辑如果出现算法复杂的问题，不要延续之前的程序设计，同样不要延续之前的数据表结构设计，要多方面思考。

2. 随着业务的变更，不仅仅是程序上的变更，还有数据结构上的变更，大量数据的处理方式与常规处理数据思维不太一样。

产品需求

这次的具体问题，我先从产品需求说起，并从头聊我踩过的坑。

1. 用户撒码可以根据这个码知道商品流向，系统有几层代理商，在商品到达用户手中之后，用户可以根据扫码查询整个商品的流向，经过了哪些代理商手中；

2. 代理商扫码可以进行发货，根据这个追溯码标记商品发货状态，同时存储代理商发货状态。

3. 追溯码有三个码类，分为大码、中码、小码，类似于一个大码对应一箱货物，一个中码对应一箱货物中的一盒，一个小码对应一盒货物中的某一件具体货物；也就是说大码涵盖中码，中码涵盖小码，需要通过上层的追溯码找到整个关系链。

4. 商户根据需要，进行追溯码导入，一次性导入的数据条数大约为10万条左右，也可能更多，也可能更少。

踩坑

原本的数据表结构设计

    admin_id #商户ID
    product_id #商品ID
    security_code #追溯码
    code_type #追溯码类型：1大码，2中码，3小码
    parent_id #当前追溯码的上级ID，如小码对应的上级中码ID
    top_id #顶级ID，对应的为大码的ID，中码和小码都要村粗
    is_use #此追溯码是否已使用
    is_sell_out #是否已经售出
    created_time #创建时间
    updated_time #更新时间
    is_deleted #是否删除

原程序设计思想

整套数据表结构的设计逻辑是以 parent_id 来进行关系关联，然后通过code_type来标明追溯码的类型，在导入的时候，先必须村粗父级，然后再存储子级，层层遍历导入。

此设计产生的问题

由于必须层层遍历导入，就会造成导入相当缓慢，必须先插入富级ID，然后再插入子级ID，因为子级ID关联了父级ID。这样也就不能采用mysql的

INSERT INTO table(field1,field2) VALUES('a', 1), ('b', 1), ('c', 1);

这种批量插入的方式进行插入，这样的话，数据插入就会很缓慢。

经过我后面的思考，把最后一级，也就是小码采用了批量插入，但是发现效率还是并不高，就算把小码改为批量插入，一次性批量插入的数据也仅有20条左右，对效率提高并不大。

新表设计方式

新的设计，我把整个表拆分成了两个表，一个表存储关系链，一个表用来做追溯标记和查询，大致如下：

关系表

admin_id #商户ID
product_id #商品ID
code_max # 大码
code_middle #中码
code_min #小码

追溯码标记表

admin_id #商户ID
product_id #商品ID
code #追溯码，不管大码中码还是小码都会建一条数据存放在此字段
is_use # 是否使用
is_sell_out #是否出售
create_time #创建时间
update_time # 更新时间
is_delete #是否已删除

新的逻辑说明

1. 首先，在关系表里存储的是关系，大码、中码、小码都完整的村粗，可以通过大码ID查询到下面的所有中码、小码；

2. 追溯码标记表，这个表专门管查询和标记追溯码状态等。

3. 新的设计方式，会增加数据表数据的管理成本，也会增加数据量大小。

4. 新的数据表关系数据的数据结构类似于这样子：

大码	中码	小码
1	11	111
1	11	112
1	12	123
1	12	124
2	21	211
2	21	212
2	22	221
2	22	222

这个表结构的说明是避免对于关系链的数据逻辑不理解做的一个示例。

整个表经过这样的重新设计和梳理了之后，每次同时插入两个表，并且可以用MySQL的批量插入操作进行插入，插入速度具有非常大的提升。

数据重复对比

在这套产品设计之中，整套的追溯码是不能重复的，经过了多方面思考，最后采用了in查询机制，整套思路大致如下：

每次遍历要导入的数据中的100条，然后用mysql的in去查询，如果取到数据，则遍历数据标记哪一条重复。不断的循环遍历去取，然后标记输出到CSV中，遍历结束后，把标记重复的文档返回给用户，让用户修改好后再进行上传。

不过关于这个数据对比的，我没有更好的思路了，测试了18W条导入数据和数据表中的25W条数据对比，花的时间大约为13秒左右，就我的预估对比次数应该为18W*25W，如果谁在这方面有更好的数据对比算法，请留言告诉我，不胜感激。

仅以此文纪念自己踩过的坑，也同样希望后人看到这篇文章后能得到一些解决思路。